﻿Imports System.Data.SqlClient

Public Class frmRPersonal

    Private Sub frmRPersonal_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System

        CargaDepartamentos()
        CargaPuestos()
        CargaStatus()

        lstDepartamentos.Items.Clear()
        lstPuestos.Items.Clear()
        lstStatus.Items.Clear()

    End Sub
    Sub CargaDepartamentos()
        lstDepartamentosDisponibles.Items.Clear()

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        Dim strSql As String = ""
        strSql = "SELECT Descripcion FROM Departamentos ORDER BY Descripcion"

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Do While rdBuscar.Read
            lstDepartamentosDisponibles.Items.Add(rdBuscar("Descripcion"))
        Loop


    End Sub
    Sub CargaPuestos()
        lstPuestosDisponibles.Items.Clear()

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        Dim strSql As String = ""
        strSql = "SELECT Descripcion FROM Puestos ORDER BY Descripcion"

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Do While rdBuscar.Read
            lstPuestosDisponibles.Items.Add(rdBuscar("Descripcion"))
        Loop
    End Sub
    Sub CargaStatus()
        lstStatusDisponibles.Items.Clear()

        lstStatusDisponibles.Items.Add("Alta")
        lstStatusDisponibles.Items.Add("Baja")
        lstStatusDisponibles.Items.Add("Reingreso")
    End Sub
    Sub CreaReporte()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6
        Dim i As Integer

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim strSql As String = ""


        strSql = " Select IDEmpleado,D.Descripcion as depto,P.Descripcion as puesto,Codigo,e.Nombre,ApellidoPaterno,FechaReingreso,ApellidoMaterno,Nombrecompleto,FechaNacimiento,LugarNacimiento,EstadoCivil,Sexo,CURP,CURPF,NSS,RFC,Homoclave,CuentaPago,EstadoEmpleado,SD,SDI,FechaAlta,CuentaCW,Telefono,CodigoPostal,Direccion,Poblacion,Estado,FechaBaja,CausaBaja,celular,telefonorecados,escolaridad,dependientes,religion" & _
               " FROM Empleados E " & _
               "INNER JOIN Departamentos D ON E.IdDepartamento=D.IdDepartamento " & _
               "INNER JOIN Puestos P ON E.IdPuesto=P.IdPuesto " & _
               "WHERE IdEmpleado>0 "

        Dim t As Integer = 0

        If lstDepartamentos.Items.Count > 0 Then
            Dim IdDepartamento As Integer
            t = lstDepartamentos.Items.Count
            Dim strDepartamentos As String = ""

            For i = 0 To t - 2
                IdDepartamento = BuscarIntDatoCitra("Departamentos", "IdDepartamento", "Descripcion='" & lstDepartamentos.Items(i).ToString.Trim & "'")
                strDepartamentos = strDepartamentos & IdDepartamento & ","
            Next
            IdDepartamento = BuscarIntDatoCitra("Departamentos", "IdDepartamento", "Descripcion='" & lstDepartamentos.Items(i).ToString.Trim & "'")
            strDepartamentos = strDepartamentos & IdDepartamento

            strSql = strSql & " AND E.IdDepartamento IN (" & strDepartamentos & ") "
        End If

        If lstPuestos.Items.Count > 0 Then
            Dim IdPuesto As Integer
            t = lstPuestos.Items.Count
            Dim strPuestos As String = ""

            For i = 0 To t - 2
                IdPuesto = BuscarIntDatoCitra("Puestos", "IdPuesto", "Descripcion='" & lstPuestos.Items(i).ToString.Trim & "'")
                strPuestos = strPuestos & IdPuesto & ","
            Next
            IdPuesto = BuscarIntDatoCitra("Puestos", "IdPuesto", "Descripcion='" & lstPuestos.Items(i).ToString.Trim & "'")
            strPuestos = strPuestos & IdPuesto

            strSql = strSql & " AND E.IdPuesto IN (" & strPuestos & ") "
        End If
        i = 0
        If lstStatus.Items.Count > 0 Then
            Dim Status As String
            t = lstStatus.Items.Count
            Dim strStatus As String = ""

            For i = 0 To t - 2
                If lstStatus.Items(i).ToString.Trim = "Alta" Then
                    strStatus = strStatus & "'A'" & " OR E.EstadoEmpleado="
                ElseIf lstStatus.Items(i).ToString.Trim = "Baja" Then
                    strStatus = strStatus & "'B'" & " OR E.EstadoEmpleado="
                Else
                    strStatus = strStatus & "'R'" & " OR E.EstadoEmpleado="
                End If

            Next
            If lstStatus.Items(i).ToString.Trim = "Alta" Then
                strStatus = strStatus & "'A'"
            ElseIf lstStatus.Items(i).ToString.Trim = "Baja" Then
                strStatus = strStatus & "'B'"
            Else
                strStatus = strStatus & "'R'"
            End If

            strSql = strSql & " AND E.EstadoEmpleado =" & strStatus & " "
        End If

        strSql = strSql & " ORDER BY NombreCompleto"

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        wb.SHEETS(1).cells(rng, 2).value = "Codigo"
        wb.SHEETS(1).cells(rng, 3).value = "Nombre"
        wb.SHEETS(1).cells(rng, 4).value = "Apellido Paterno"
        wb.SHEETS(1).cells(rng, 5).value = "Apellido Materno"
        wb.SHEETS(1).cells(rng, 6).value = "Nombre Completo"
        wb.SHEETS(1).cells(rng, 7).value = "Fecha Nacimiento"
        wb.SHEETS(1).cells(rng, 8).value = "Lugar Nacimiento"
        wb.SHEETS(1).cells(rng, 9).value = "Estado Civil"
        wb.SHEETS(1).cells(rng, 10).value = "Sexo"
        wb.SHEETS(1).cells(rng, 11).value = "CURP"
        wb.SHEETS(1).cells(rng, 12).value = "No. IMSS"
        wb.SHEETS(1).cells(rng, 13).value = "RFC"
        wb.SHEETS(1).cells(rng, 14).value = "Cuenta Banco"
        wb.SHEETS(1).cells(rng, 15).value = "SD"
        wb.SHEETS(1).cells(rng, 16).value = "SDI"
        wb.SHEETS(1).cells(rng, 17).value = "Departamento"
        wb.SHEETS(1).cells(rng, 18).value = "Puesto"
        wb.SHEETS(1).cells(rng, 19).value = "Cuenta Compaq"
        wb.SHEETS(1).cells(rng, 20).value = "Direccion"
        wb.SHEETS(1).cells(rng, 21).value = "Codigo Postal"
        wb.SHEETS(1).cells(rng, 22).value = "Ciudad"
        wb.SHEETS(1).cells(rng, 23).value = "Estado"
        wb.SHEETS(1).cells(rng, 24).value = "Telefono"
        wb.SHEETS(1).cells(rng, 25).value = "Tel Celular"
        wb.SHEETS(1).cells(rng, 26).value = "Tel Recados"
        wb.SHEETS(1).cells(rng, 27).value = "Escolaridad"
        wb.SHEETS(1).cells(rng, 28).value = "Estado Empleado"
        wb.SHEETS(1).cells(rng, 29).value = "Fecha Alta"
        wb.SHEETS(1).cells(rng, 30).value = "Fecha Reingreso"
        wb.SHEETS(1).cells(rng, 31).value = "Fecha Baja"
        wb.SHEETS(1).cells(rng, 32).value = "Causa de la Baja"
        wb.SHEETS(1).cells(rng, 33).value = "Tarjeton"
        wb.SHEETS(1).cells(rng, 34).value = "Licencia"
        wb.SHEETS(1).cells(rng, 35).value = "Vigencia"
        wb.SHEETS(1).cells(rng, 36).value = "No. Dependientes"
        wb.SHEETS(1).cells(rng, 37).value = "Religion"

        For i = 2 To 37
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).font.size = 9
        Next

        Dim fNac As Date
        Dim dia As String = ""
        Dim mes As String = ""
        Dim strFnac As String = ""
        Dim sexo As String = ""
        Dim estado As String = ""
        Dim ALTA As Date
        Dim re As Date
        Dim baja As Date
        Dim Civil As String = ""
        Dim anio As String = ""
        Dim vigencia As Date


        rng = rng + 1
        Dim cont As Integer = 0
        Do While rdBuscar.Read()
            cont = cont + 1
            dia = ""
            mes = ""
            strFnac = ""
            sexo = ""
            estado = ""
            Civil = ""
            anio = ""
            wb.SHEETS(1).cells(rng, 2).value = "'" & rdBuscar("Codigo").ToString.Trim
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("Nombre").ToString.Trim
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("ApellidoPaterno").ToString.Trim
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("ApellidoMaterno").ToString.Trim
            wb.SHEETS(1).cells(rng, 6).value = rdBuscar("NombreCompleto").ToString.Trim
            fNac = rdBuscar("FechaNacimiento")
            dia = fNac.Day
            mes = fNac.Month
            If dia < 10 Then
                dia = "0" & dia
            End If
            If mes < 10 Then
                mes = "0" & mes
            End If
            anio = Mid(fNac.Year, 3, 2)
            strFnac = anio & mes & dia
            wb.SHEETS(1).cells(rng, 7).value = fNac.Day & "/" & fNac.Month & "/" & fNac.Year
            wb.SHEETS(1).cells(rng, 8).value = rdBuscar("LugarNacimiento").ToString.Trim
            If rdBuscar("EstadoCivil").ToString.Trim = "C" Then
                Civil = "Casado"
            ElseIf rdBuscar("EstadoCivil").ToString.Trim = "S" Then
                Civil = "Soltero"
            ElseIf rdBuscar("EstadoCivil").ToString.Trim = "D" Then
                Civil = "Divorciado"
            ElseIf rdBuscar("EstadoCivil").ToString.Trim = "V" Then
                Civil = "Viudo"

            End If

            wb.SHEETS(1).cells(rng, 9).value = Civil
            If rdBuscar("Sexo").ToString.Trim = "M" Then
                sexo = "Masculino"
            Else
                sexo = "Femenino"
            End If
            wb.SHEETS(1).cells(rng, 10).value = sexo
            wb.SHEETS(1).cells(rng, 11).value = rdBuscar("CURP").ToString.Trim & strFnac & rdBuscar("CURPF").ToString.Trim
            wb.SHEETS(1).cells(rng, 12).value = rdBuscar("Nss").ToString.Trim
            wb.SHEETS(1).cells(rng, 13).value = rdBuscar("RFC").ToString.Trim & strFnac & rdBuscar("Homoclave").ToString.Trim
            wb.SHEETS(1).cells(rng, 14).value = rdBuscar("CuentaPago")
            wb.SHEETS(1).cells(rng, 15).value = BuscarDblDatoNominas("SELECT SueldoDiario FROM NOM10001 WHERE CodigoEmpleado='" & rdBuscar("Codigo") & "'")
            wb.SHEETS(1).cells(rng, 16).value = BuscarDblDatoNominas("SELECT SueldoIntegrado FROM NOM10001 WHERE CodigoEmpleado='" & rdBuscar("Codigo") & "'")
            wb.SHEETS(1).cells(rng, 17).value = rdBuscar("depto").ToString.Trim
            wb.SHEETS(1).cells(rng, 18).value = rdBuscar("puesto").ToString.Trim
            wb.SHEETS(1).cells(rng, 19).value = rdBuscar("CuentaCW").ToString.Trim
            wb.SHEETS(1).cells(rng, 20).value = rdBuscar("Direccion").ToString.Trim
            wb.SHEETS(1).cells(rng, 21).value = rdBuscar("CodigoPostal")
            wb.SHEETS(1).cells(rng, 22).value = rdBuscar("Poblacion").ToString.Trim
            wb.SHEETS(1).cells(rng, 23).value = rdBuscar("Estado").ToString.Trim
            wb.SHEETS(1).cells(rng, 24).value = rdBuscar("Telefono").ToString.Trim
            wb.SHEETS(1).cells(rng, 25).value = rdBuscar("celular").ToString.Trim
            wb.SHEETS(1).cells(rng, 26).value = rdBuscar("telefonorecados").ToString.Trim
            wb.SHEETS(1).cells(rng, 27).value = rdBuscar("Escolaridad").ToString.Trim
            If rdBuscar("EstadoEmpleado").ToString.Trim = "A" Then
                estado = "Alta"
            ElseIf rdBuscar("EstadoEmpleado").ToString.Trim = "B" Then
                estado = "Baja"
            Else
                estado = "Reingreso"
            End If
            wb.SHEETS(1).cells(rng, 28).value = estado
            ALTA = rdBuscar("FechaAlta")
            wb.SHEETS(1).cells(rng, 29).value = ALTA.Day & "/" & ALTA.Month & "/" & ALTA.Year
            Try
                re = rdBuscar("FechaReingreso")
                baja = rdBuscar("FechaBaja")
            Catch ex As Exception

            End Try
            If estado <> "Alta" Then
                wb.SHEETS(1).cells(rng, 30).value = re.Day & "/" & re.Month & "/" & re.Year
                wb.SHEETS(1).cells(rng, 31).value = baja.Day & "/" & baja.Month & "/" & baja.Year

            End If

            wb.SHEETS(1).cells(rng, 32).value = rdBuscar("CausaBaja").ToString.Trim

            If UCase(rdBuscar("puesto").ToString.Trim) = "OPERADOR" Then
                wb.SHEETS(1).cells(rng, 33).value = BuscarStrDatoCitra("Capacitacion", "Tarjeton", "status='ALTA' and idempleado=" & rdBuscar("idempleado"))
                wb.SHEETS(1).cells(rng, 34).value = BuscarStrDatoCitra("Licencias", "Licencia", "idempleado=" & rdBuscar("idempleado"))
                vigencia = BuscarDateDatoCitra("Licencias", "vigencia", "idempleado=" & rdBuscar("idempleado"))
                wb.SHEETS(1).cells(rng, 35).value = vigencia.Day & "/" & vigencia.Month & "/" & vigencia.Year
            Else
                wb.SHEETS(1).cells(rng, 33).value = ""
                wb.SHEETS(1).cells(rng, 34).value = ""
                wb.SHEETS(1).cells(rng, 35).value = ""
            End If
          
            wb.SHEETS(1).cells(rng, 36).value = rdBuscar("dependientes")
            wb.SHEETS(1).cells(rng, 37).value = rdBuscar("Religion")
            For i = 1 To 37
                wb.SHEETS(1).cells(rng, i).font.size = 9
            Next
            rng = rng + 1
        Loop

        wb.SHEETS(1).columns(15).numberformat = "#,##0.00"
        wb.SHEETS(1).columns(16).numberformat = "#,##0.00"

        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 8
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 22
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(6).ColumnWidth = 40
        wb.SHEETS(1).COLUMNS(7).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(8).ColumnWidth = 20
        wb.SHEETS(1).COLUMNS(9).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(10).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(11).ColumnWidth = 25
        wb.SHEETS(1).COLUMNS(12).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(13).ColumnWidth = 25
        wb.SHEETS(1).COLUMNS(14).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(15).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(16).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(17).ColumnWidth = 32
        wb.SHEETS(1).COLUMNS(18).ColumnWidth = 30
        wb.SHEETS(1).COLUMNS(19).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(20).ColumnWidth = 48
        wb.SHEETS(1).COLUMNS(21).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(22).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(23).ColumnWidth = 20
        wb.SHEETS(1).COLUMNS(24).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(25).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(26).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(27).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(28).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(29).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(30).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(31).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(32).ColumnWidth = 40
        wb.SHEETS(1).COLUMNS(33).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(34).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(35).ColumnWidth = 12

        wb.SHEETS(1).cells(1, 2).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 2).FONT.SIZE = 20
        wb.SHEETS(1).cells(1, 2).FONT.name = "Segoe UI Light"
        wb.SHEETS(1).cells(2, 2).value = "Informe de Personal Detallado: " & cont & " Registros en el filtro"
        wb.SHEETS(1).cells(2, 2).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 2).FONT.name = "Segoe UI Light"
        wb.SHEETS(1).cells(3, 2).value = "Impreso el " & Now().Day & "-" & Now().Month & "-" & Now().Year & " por " & UsuarioActivo
        wb.SHEETS(1).cells(3, 3).FONT.SIZE = 12
        wb.SHEETS(1).cells(3, 3).FONT.name = "Segoe UI Light"

        wb.SHEETS(1).cells(1, 1).value = ""
        wb.SHEETS(1).columns(1).columnwidth = 3
        wb.sheets(1).cells(1, 1).interior.color = 15773696
        wb.sheets(1).cells(2, 1).interior.color = 15773696


    End Sub

    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
        Close()

    End Sub

    Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
        CreaReporte()

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim aux As String = ""
        aux = lstDepartamentosDisponibles.Text.ToString.Trim
        If aux <> "" Then
            lstDepartamentos.Items.Add(lstDepartamentosDisponibles.SelectedItem)
            lstDepartamentosDisponibles.Items.Remove(lstDepartamentosDisponibles.SelectedItem)
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim aux As String = ""
        aux = lstPuestosDisponibles.Text.ToString.Trim
        If aux <> "" Then
            lstPuestos.Items.Add(lstPuestosDisponibles.SelectedItem)
            lstPuestosDisponibles.Items.Remove(lstPuestosDisponibles.SelectedItem)
        End If
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        Dim aux As String = ""
        aux = lstStatusDisponibles.Text.ToString.Trim
        If aux <> "" Then
            lstStatus.Items.Add(lstStatusDisponibles.SelectedItem)
            lstStatusDisponibles.Items.Remove(lstStatusDisponibles.SelectedItem)
        End If
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim aux As String = ""
        aux = lstDepartamentos.Text.ToString.Trim
        If aux <> "" Then
            lstDepartamentosDisponibles.Items.Add(lstDepartamentos.SelectedItem)
            lstDepartamentos.Items.Remove(lstDepartamentos.SelectedItem)
        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim aux As String = ""
        aux = lstPuestos.Text.ToString.Trim
        If aux <> "" Then
            lstPuestosDisponibles.Items.Add(lstPuestos.SelectedItem)
            lstPuestos.Items.Remove(lstPuestos.SelectedItem)
        End If
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim aux As String = ""
        aux = lstStatus.Text.ToString.Trim
        If aux <> "" Then
            lstStatusDisponibles.Items.Add(lstStatus.SelectedItem)
            lstStatus.Items.Remove(lstStatus.SelectedItem)
        End If
    End Sub
End Class